﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class CHUYENMUCBLL
    {
        DataService DB = new DataService();
        PHUMUCBLL pmBll = new PHUMUCBLL();
        COMMENTBLL cmeBll = new COMMENTBLL();
        BAIVIETBLL bvBll = new BAIVIETBLL();

        public int getCountOfBaivietWithIdChuyenmuc(int machuyenmuc) {
            string sql = "select count(*) from chuyenmuc cm,baiviet bv,phumuc pm"
                        + " where cm.machuyenmuc = pm.machuyenmuc and pm.maphumuc = bv.maphumuc"
                        + " and cm.machuyenmuc = " + machuyenmuc.ToString();

            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        //get all values
        public List<CHUYENMUC> getALL()
        {
            string sql = "select * from chuyenmuc";
            DataTable table = new DataTable();
            table = DB.ExecuteReader(sql);

            List<CHUYENMUC> ListCm = new List<CHUYENMUC>();
            foreach (DataRow r in table.Rows)
            {
                CHUYENMUC vt = new CHUYENMUC();
                vt.machuyenmuc = (int)r[0];
                vt.tenchuyenmuc = (string)r[1];
                try
                {
                    vt.mota = (string)r[2];
                }
                catch { vt.mota = " "; }
                vt.matinhtrangcm = (int)r[3];

                ListCm.Add(vt);
            }
            return ListCm;
        }

        //insert value
        public void insert(string tenchuyenmuc, string mota, int matinhtrangcm)
        {

            string sql = "insert into chuyenmuc values (@tenchuyenmuc,@mota,@matinhtrangcm)";
            SqlParameter paraTen = new SqlParameter("tenchuyenmuc", tenchuyenmuc);
            SqlParameter paraMota = new SqlParameter("mota", mota);
            SqlParameter paraMattcm = new SqlParameter("matinhtrangcm", matinhtrangcm);

            DB.ExecuteNonQuery(sql, paraTen, paraMota, paraMattcm);

        }

        public void update(int machuyenmuc, string tenchuyenmuc, string mota, int matinhtrangcm)
        {
            string sql = "update chuyenmuc set tenchuyenmuc = N'" + tenchuyenmuc + "' ,mota = N" + mota + " , matinhtrangcm = " + matinhtrangcm.ToString() + " where machuyenmuc = " + machuyenmuc.ToString();
            DB.ExecuteNonQuery(sql);
        }

        public void delete(int machuyenmuc)
        {
            string sql = "delete from chuyenmuc where machuyenmuc = " + machuyenmuc.ToString();
            DB.ExecuteNonQuery(sql);
        }

        //get count
        public int count()
        {
            string sql = "select count(*) from chuyenmuc";
            return Int32.Parse(DB.ExecuteScalar(sql));
        }

        //get html for innerHTML
        public string getHTMLForHomePage() {
            List<CHUYENMUC> ListCm = new List<CHUYENMUC>();
            ListCm = this.getALL();

            string str = "";

            foreach(CHUYENMUC cm in ListCm){
                List<PHUMUC> listPm = pmBll.getPhumucWithIDCm(cm.machuyenmuc);

                str += "<div class=\"services_box\">"
                + "<div class=\"service_title\">"
                + "<img src=\"images/icon/001_43.ico\" alt=\"\" title=\"\" style=\"padding-left: 10px; padding-top: 12px; float: left;\"/>"
                + "<h2>&nbsp; "
                + cm.tenchuyenmuc + " - " + cm.mota
                + "</h2>"
                + "</div>"
                + "<div class=\"con_bg_top\"></div>"
                + "<div class=\"col_bg\">"
                + "<div class=\"col_grad_bot\">"
                + "<div class=\"col_grad_top\">"
                + "<div style=\"clear: both\">";

                str += "<div class=\"aboutus_bg\">"
                    + "<table>"
                    + "<tr>"
                    + "<td class=\"about_col0\"><div class=\"about_div_default\">TT</div></td>"
                    + "<td class=\"about_col1\"><div class=\"about_div_default\">Phụ mục</div></td>"
                    + "<td class=\"about_col2\"><div class=\"about_div_default\">Bài mới gửi</div></td>"
                    + "<td class=\"about_col3\"><div class=\"about_div_default\">Đề tài</div></td>"
                    + "<td class=\"about_col4\"><div class=\"about_div_default\">Bài gửi</div></td>"
                    + "</tr>";
                    foreach(PHUMUC pm in listPm){

                        if (pmBll.getTinhtrangWithID(pm.maphumuc) == "Block")
                        {
                            str += "<tr>"
                                + "<td class=\"about_col0_block\"><div class=\"normal_div_boder\"><img src=\"images/icon/001_41.ico\"/></div></td>"
                                + "<td class=\"about_col1_block\"><div class=\"normal_div_boder\"><p>"
                                + pm.tenphumuc + " - Tạm khóa"
                                + "</p></div></td>";

                                BAIVIET bv = bvBll.getBestNewBaivietWithIdPhumuc(pm.maphumuc);
                                if(bv != null){
                                    str += "<td class=\"about_col2_block\"><div class=\"normal_div_boder\"><a href=\"Baiviet.aspx?mabv=" + bv.mabaiviet + "&tenbv=" + bv.tenbaiviet + "\" style=\"text-decoration:none\">" + bv.tenbaiviet + "</a></div></td>";
                                }
                                else{
                                    str += "<td class=\"about_col2_block\"><div class=\"normal_div_boder\">Chưa có bài viết nào !</div></td>";
                                }
                                
                                str += "<td class=\"about_col3_block\"><div class=\"normal_div_boder\">" + pmBll.getCountOfBaivietByIdPhumuc(pm.maphumuc) +  "</div></td>"
                                + "<td class=\"about_col4_block\"><div class=\"normal_div_boder\">" + cmeBll.getCountOfCommentByIDPhumuc(pm.maphumuc) + "</div></td>"
                                + "</tr>";
                        }
                        else {
                            str += "<tr>"
                                + "<td class=\"about_col0\"><div class=\"normal_div_boder\"></div></td>"
                                + "<td class=\"about_col1\"><div class=\"normal_div_boder\"><p>"
                                + "<a href=\"Phumuc.aspx?macm=" + pm.maphumuc + "\"runat=\"server\">" + pm.tenphumuc + "</a>"
                                + "</p></div></td>";

                                BAIVIET bv = bvBll.getBestNewBaivietWithIdPhumuc(pm.maphumuc);
                                if(bv != null){
                                    str += "<td class=\"about_col2\"><div class=\"normal_div_boder\"><a href=\"Baiviet.aspx?mabv=" + bv.mabaiviet + "&tenbv=" + bv.tenbaiviet + "\" style=\"text-decoration:none\">" + bv.tenbaiviet + "</a></div></td>";
                                }
                                else{
                                    str += "<td class=\"about_col2\"><div class=\"normal_div_boder\">Chưa có bài viết nào !</div></td>";
                                }
                                
                                str += "<td class=\"about_col3\"><div class=\"normal_div_boder\">" + pmBll.getCountOfBaivietByIdPhumuc(pm.maphumuc) +  "</div></td>"
                                + "<td class=\"about_col4\"><div class=\"normal_div_boder\">" + cmeBll.getCountOfCommentByIDPhumuc(pm.maphumuc) + "</div></td>"
                                + "</tr>";
                        }
                    }
                str +="</table>"
                    + "</div>";



                str +="</div>"
                +"</div>" 
                +"</div>"
                +"</div>"
                +"<div class=\"con_bg_bot\"></div>"
                +"</div>"
                +"</br>";
            }
            return str;
        }
    }
}
